서브쿼리와 JOIN 중 무엇을 선택할까

목차

• #문제가 되는 상황
• #같은 질문을 여러 SQL로 표현할 수 있다
• #존재 여부는 EXISTS가 자연스럽다
• #JOIN은 오른쪽 컬럼이 필요할 때 사용한다
• #NOT EXISTS와 NOT IN의 NULL 차이
• #상관 서브쿼리가 반복 실행되는가
• #scalar 서브쿼리의 단일 행 계약
• #먼저 집계한 뒤 JOIN하기
• #옵티마이저가 query를 다시 쓸 수 있다
• #EXPLAIN ANALYZE로 비교하는 방법
• #실전 점검 목록
• #결론
• #관련 노트

문제가 되는 상황

“서브쿼리는 행마다 실행되므로 느리고 JOIN이 빠르다”는 말을 자주 듣는다. 하지만 현대 DB optimizer는 EXISTS를 semi join으로 바꾸거나 서브쿼리를 펼칠 수 있다. 반대로 JOIN으로 바꾼 query가 1:N 관계 때문에 행을 크게 늘리고 마지막에 DISTINCT로 정리하면서 더 비싸질 수도 있다.

SQL 표현을 고를 때는 먼저 질문의 의미를 본다. 오른쪽 행이 존재하는지만 필요한지, 오른쪽 컬럼을 결과에 붙일지, 행별 단일 값을 계산할지, 여러 행을 먼저 집계할지에 따라 자연스러운 형태가 다르다. 마지막 성능 판단은 실제 DB 버전과 데이터 분포의 실행 계획으로 한다.

같은 질문을 여러 SQL로 표현할 수 있다

“주문한 적 있는 사용자”는 EXISTS로 표현할 수 있다.

SELECT u.id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

JOIN과 DISTINCT로도 같은 사용자 집합을 만들 수 있다.

SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id;

두 SQL의 의미는 비슷하지만 중간 결과가 다르다. 사용자가 주문 100개를 가지면 JOIN은 사용자 행을 100개로 펼친 뒤 DISTINCT로 합칠 수 있다. EXISTS는 일치 여부만 필요하다는 의도를 표현해 첫 일치를 찾은 뒤 더 볼 필요가 없을 수 있다.

실제 optimizer가 어떤 계획을 선택하는지는 index, 통계, DB 버전과 query 전체에 따라 달라진다.

존재 여부는 EXISTS가 자연스럽다

EXISTS 안의 select list 값은 존재 판정에 사용되지 않는다.

SELECT p.id, p.name
FROM products p
WHERE EXISTS (
  SELECT 1
  FROM inventory i
  WHERE i.product_id = p.id
    AND i.available_quantity > 0
);

의도는 “재고 행의 컬럼을 보여 달라”가 아니라 “판매 가능한 재고가 하나라도 있는 상품인가”다. (product_id, available_quantity) 또는 query에 맞는 index가 있으면 효율적인 lookup이 가능하다.

EXISTS를 application loop의 N+1 query로 대체하지 않는다.

// 좋지 않은 예: 사용자 수만큼 DB 요청이 추가된다.
for (const user of users) {
  user.hasOrder = await orderRepository.existsByUserId(user.id);
}

DB 내부의 correlated EXISTS는 optimizer가 하나의 query plan으로 처리할 기회가 있지만 application loop는 network round trip을 반복한다.

JOIN은 오른쪽 컬럼이 필요할 때 사용한다

주문 번호와 금액을 실제 결과에 포함하려면 JOIN이 자연스럽다.

SELECT
  u.id AS user_id,
  u.name,
  o.id AS order_id,
  o.total_minor
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

결과의 단위는 user가 아니라 user-order 조합이다. 한 사용자가 여러 행으로 나오는 것이 요구에 맞는지 확인한다.

사용자별 최신 주문 한 건만 필요하다면 단순 JOIN만으로는 충분하지 않다. window function이나 적절한 lateral/derived query를 사용할 수 있다.

WITH ranked_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY o.user_id
      ORDER BY o.created_at DESC, o.id DESC
    ) AS row_num
  FROM orders o
)
SELECT u.id, u.name, r.id AS latest_order_id
FROM users u
LEFT JOIN ranked_orders r
  ON r.user_id = u.id
 AND r.row_num = 1;

DB의 window function materialization과 index 사용을 실행 계획으로 확인한다.

NOT EXISTS와 NOT IN의 NULL 차이

주문이 없는 사용자를 찾을 때 NOT EXISTS는 의도가 분명하다.

SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

다음 NOT IN은 서브쿼리 결과에 NULL이 하나라도 있으면 예상과 다른 결과를 낼 수 있다.

SELECT u.id, u.name
FROM users u
WHERE u.id NOT IN (
  SELECT o.user_id
  FROM orders o
);

SQL의 three-valued logic에서 value NOT IN (1, NULL)은 true로 확정되지 않는다. orders.user_id가 NOT NULL constraint로 보장되지 않는다면 특히 위험하다.

antijoin 의도에는 보통 NOT EXISTS가 읽기 쉽다. DB별 계획과 nullable constraint를 함께 확인한다.

상관 서브쿼리가 반복 실행되는가

상관 서브쿼리는 바깥 query의 현재 행 값을 참조한다.

SELECT
  u.id,
  u.name,
  (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.user_id = u.id
  ) AS order_count
FROM users u;

논리적으로는 사용자마다 주문 수를 계산한다. optimizer가 decorrelation하거나 index lookup을 반복할 수 있다. 사용자 10명에는 충분하지만 100만 명과 index 없는 orders에는 큰 비용이 될 수 있다.

미리 집계한 결과를 JOIN하는 대안이 있다.

SELECT
  u.id,
  u.name,
  COALESCE(s.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) s ON s.user_id = u.id;

어느 쪽이 더 빠른지는 users 필터 선택도에 따라 달라진다. 사용자 한 명을 조회한다면 index correlated lookup이 전체 orders 집계보다 훨씬 작을 수 있다. query 범위와 cardinality를 함께 본다.

scalar 서브쿼리의 단일 행 계약

SELECT list의 scalar 서브쿼리는 한 행·한 값만 반환해야 한다.

SELECT
  p.id,
  (
    SELECT c.name
    FROM categories c
    WHERE c.id = p.category_id
  ) AS category_name
FROM products p;

category ID가 unique이므로 한 행 계약이 분명하다. 반대로 조건이 여러 행을 반환할 수 있으면 DB 오류가 나거나 DB별 동작에 의존하게 된다.

-- 여러 주문 중 어느 하나인지 정의하지 않은 잘못된 예
SELECT (
  SELECT o.id
  FROM orders o
  WHERE o.user_id = u.id
) AS order_id
FROM users u;

LIMIT 1만 붙여 오류를 숨기면 어떤 행인지 비결정적이다. 최신 주문이라면 ORDER BY created_at DESC, id DESC LIMIT 1처럼 의미를 완전히 정의한다.

먼저 집계한 뒤 JOIN하기

두 개의 1:N 관계를 동시에 JOIN하면 행이 곱해져 집계가 부풀 수 있다. 사용자별 주문 합계와 환불 합계를 각각 집계한 뒤 붙인다.

WITH order_totals AS (
  SELECT user_id, SUM(total_minor) AS paid_total
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
),
refund_totals AS (
  SELECT user_id, SUM(amount_minor) AS refund_total
  FROM refunds
  GROUP BY user_id
)
SELECT
  u.id,
  COALESCE(o.paid_total, 0) AS paid_total,
  COALESCE(r.refund_total, 0) AS refund_total
FROM users u
LEFT JOIN order_totals o ON o.user_id = u.id
LEFT JOIN refund_totals r ON r.user_id = u.id;

CTE는 가독성을 높이지만 DB 버전과 설정에 따라 inline되거나 materialize될 수 있다. CTE라는 문법만으로 성능을 예측하지 않는다.

옵티마이저가 query를 다시 쓸 수 있다

SQL은 절차 코드가 아니라 원하는 결과를 선언한다. optimizer는 다음 변환을 할 수 있다.

• EXISTS/IN을 semi join으로 변환
• 상관 서브쿼리를 decorrelate
• join 순서 변경
• predicate pushdown
• subquery materialization
• 불필요한 join 제거

따라서 query text에서 서브쿼리가 안쪽에 있다고 반드시 바깥 행마다 처음부터 실행되는 것은 아니다. 반대로 optimizer가 통계 부족이나 복잡한 조건 때문에 기대한 변환을 못 할 수도 있다.

optimizer 통계가 오래되었거나 컬럼 값 분포가 치우치면 잘못된 cardinality를 추정할 수 있다. query rewrite 전에 통계와 parameter sensitivity도 확인한다.

EXPLAIN ANALYZE로 비교하는 방법

두 표현을 비교할 때 결과가 정말 같은지부터 테스트한다.

(
  SELECT u.id
  FROM users u
  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
)
EXCEPT
(
  SELECT DISTINCT u.id
  FROM users u
  JOIN orders o ON o.user_id = u.id
);

DB가 EXCEPT를 지원하지 않으면 양방향 anti join 등으로 비교한다. 그다음 대표 parameter와 데이터 분포에서 실행 계획을 본다.

EXPLAIN ANALYZE
SELECT ...;

확인할 항목은 다음과 같다.

• estimated rows와 actual rows 차이
• table scan·index lookup 횟수
• nested loop 안쪽이 몇 번 실행되었는지
• materialized subquery 크기
• sort·temporary table·hash memory와 spill
• 전체 반환 행과 중간 행의 cardinality
• cold/warm cache와 p95/p99 latency

한 번의 개발 DB 실행 시간만으로 결론 내리지 않고 운영과 비슷한 데이터량·분포로 여러 번 측정한다.

실전 점검 목록

존재 여부는 EXISTS가 자연스럽고 여러 테이블의 컬럼을 결합할 때는 JOIN이 읽기 쉽다. DB 옵티마이저가 둘을 비슷한 계획으로 바꿀 수도 있다.

결론

존재 여부에는 EXISTS, 오른쪽 컬럼 결합에는 JOIN, 행별 단일 값에는 scalar subquery, 여러 1:N 합계에는 사전 집계가 의도를 잘 드러낸다. NOT IN의 NULL과 JOIN의 cardinality 증폭을 주의하고 “서브쿼리는 느리다”는 문법 규칙으로 판단하지 않는다. 실제 optimizer의 semi join·decorrelation 여부와 loop 수를 EXPLAIN ANALYZE로 확인해 선택해야 한다.

관련 노트

• SQL JOIN
• MySQL 복합 인덱스의 순서와 중복 인덱스 판단하기